using MediatR;
using System.Data;
using System.IO;
using System.Threading;
using System.Threading.Tasks;
using Dapper;
using Microsoft.Extensions.Logging;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using RBAC.Interstructrue.Base;
using RBAC.Read.Api.Application.Command.ERP.PurchasingManagement.PurchaseInvoicefolder;

namespace RBAC.Read.Api.Application.Handler.ERP.PurchasingManagement.PurchaseInvoicefolder
{
    public class ExportSalesReturnDetailCommandHandler : IRequestHandler<ExportSalesReturnDetailCommands, byte[]>
    {
        private readonly IDbConnectionFactory _connectionFactory;
        private readonly ILogger<ExportSalesReturnDetailCommandHandler> _logger;

        public ExportSalesReturnDetailCommandHandler(IDbConnectionFactory connectionFactory,
            ILogger<ExportSalesReturnDetailCommandHandler> logger)
        {
            _connectionFactory = connectionFactory;
            _logger = logger;
        }

        public async Task<byte[]> Handle(ExportSalesReturnDetailCommands request, CancellationToken cancellationToken)
        {
            using var conn = _connectionFactory.CreateConnectionWithRetry();

            var sql = @"SELECT 
                            srd.Id,
                            srd.SalesOrderNumber,
                            srd.CustomerName,
                            srd.Salesperson,
                            srd.DocumentDate,
                            srd.MaterialCode,
                            srd.MaterialName,
                            srd.ReturnQuantity,
                            srd.InboundNumber,
                            srd.CreateTime
                        FROM SalesReturnDetails srd
                        WHERE srd.IsDeleted = 0
                        ORDER BY srd.Id DESC";

            var rows = await conn.QueryAsync(sql);

            IWorkbook workbook = new HSSFWorkbook();
            var sheet = workbook.CreateSheet("SalesReturnDetails");

            int r = 0;
            var header = sheet.CreateRow(r++);
            string[] headers = new[]
            {
                "销售订货号","客户名称","销售员","单据日期","物料编码","物料名称","退货数量","入库数量","创建时间"
            };
            for (int i = 0; i < headers.Length; i++) header.CreateCell(i).SetCellValue(headers[i]);

            foreach (var row in rows)
            {
                var dataRow = sheet.CreateRow(r++);
                int c = 0;
                dataRow.CreateCell(c++).SetCellValue((string?)row.SalesOrderNumber ?? "");
                dataRow.CreateCell(c++).SetCellValue((string?)row.CustomerName ?? "");
                dataRow.CreateCell(c++).SetCellValue((string?)row.Salesperson ?? "");
                dataRow.CreateCell(c++).SetCellValue((string?)row.DocumentDate ?? "");
                dataRow.CreateCell(c++).SetCellValue((string?)row.MaterialCode ?? "");
                dataRow.CreateCell(c++).SetCellValue((string?)row.MaterialName ?? "");
                dataRow.CreateCell(c++).SetCellValue((string?)row.ReturnQuantity ?? "");
                dataRow.CreateCell(c++).SetCellValue((string?)row.InboundNumber ?? "");
                // CreateTime 可能为 DateTime，根据驱动返回类型处理
                string createTimeText = "";
                try { createTimeText = row.CreateTime?.ToString("yyyy-MM-dd HH:mm:ss") ?? ""; } catch { createTimeText = (row.CreateTime ?? "").ToString(); }
                dataRow.CreateCell(c++).SetCellValue(createTimeText);
            }

            for (int i = 0; i < headers.Length; i++) sheet.AutoSizeColumn(i);

            using var ms = new MemoryStream();
            workbook.Write(ms);
            return ms.ToArray();
        }
    }
}
